﻿

* LAST UPDATE: 5/12/2023;
* ADD US MOM / REV, AND RE RUN THE PRIOR RESULT;



* Import country-month MOM / REVs;
libname home 'D:\Dropbox\Work - RA\(2023.04.03)\(2023.04.17) Correlation and market alpha\Result_table'; 
data tmp1; set home.table3_yearly_spread; run; 

		* Add US MOM REV as well (created in A0);
		libname home 'D:\Dropbox\Work - RA\(2023.04.03)\(2023.05.12) Culture (IDV, UAI) - Adding US\@DATA';
		data chk0a (keep=date spread_mom); set home.US_MOM; where _name_='EW'; rename rank11=spread_mom; run; 
		data chk0b (keep=date spread_rev); set home.US_REV; where _name_='EW'; rename rank11=spread_rev; run; 
		data chk0; merge chk0a chk0b; by date; country='US'; run; 

		* Positive correlation (consistent with other countries) = 0.266;
		proc corr data=chk0; var spread_mom spread_rev; run; 

		* Add average number of obs in portfolio;
		data chk0c;  set home.US_MOM; where _name_='num'; nobs=mean(of rank:); run; 
		proc sql;
			create table chk0 as select distinct a.*, b.nobs
			from chk0 a left join chk0c b on a.date=b.date;
		quit; 

		* Add the US data to tmp1 - note that time variable (t and date does not align, but not problem);
		data tmp1; set tmp1 chk0; run; 


* Calc monthly series of MOM and Rev for each country.   Calculate the TIMESERIES correl between MOM and REV for each country and report in EXCEL along with country.
This would be like TIMESERIESCORR(MOM,REV) for US is X TIMESERIESCorr(MOM,REV) for Switzerland is Y etc.;

		* Change the REV spread to REV "profit"; 
		data tmp1; set tmp1; spread_REV=-1*spread_REV; run; 

proc sort data=tmp1; by country; run; 
proc corr data=tmp1 out=tmp4; by country; var spread_REV spread_MOM; run; 
data tmp5; set tmp4; 
	where _type_='CORR' and _NAME_='spread_rev'; keep country spread_mom;
run; 

data tmp5; set tmp5;
	if country='AE' then country_name='UNITED ARAB EMIRATES';
	if country='AR' then country_name='ARGENTINA ';
	if country='AT' then country_name='AUSTRIA ';
	if country='AU' then country_name='AUSTRALIA ';
	if country='BE' then country_name='BELGIUM ';
	if country='BR' then country_name='BRAZIL';
	if country='CA' then country_name='CANADA';
	if country='CH' then country_name='SWITZERLAND ';
	if country='CL' then country_name='CHILE ';
	if country='CN' then country_name='CHINA ';
	if country='CO' then country_name='COLOMBIA';
	if country='CZ' then country_name='CZECH REPUBLIC';
	if country='DE' then country_name='GERMANY ';
	if country='DK' then country_name='DENMARK ';
	if country='EG' then country_name='EGYPT ';
	if country='ES' then country_name='SPAIN ';
	if country='FI' then country_name='FINLAND ';
	if country='FR' then country_name='FRANCE';
	if country='GB' then country_name='UNITED KINGDOM';
	if country='GR' then country_name='GREECE';
	if country='HK' then country_name='HONG KONG ';
	if country='HU' then country_name='HUNGARY ';
	if country='ID' then country_name='INDONESIA ';
	if country='IE' then country_name='IRELAND ';
	if country='IL' then country_name='ISRAEL';
	if country='IN' then country_name='INDIA ';
	if country='IT' then country_name='ITALY ';
	if country='JP' then country_name='JAPAN ';
	if country='KR' then country_name='KOREA ';
	if country='KW' then country_name='KUWAIT';
	if country='MX' then country_name='MEXICO';
	if country='MY' then country_name='MALAYSIA';
	if country='NL' then country_name='NETHERLANDS ';
	if country='NO' then country_name='NORWAY';
	if country='NZ' then country_name='NEW ZEALAND ';
	if country='PE' then country_name='PERU';
	if country='PH' then country_name='PHILIPPINES ';
	if country='PK' then country_name='PAKISTAN';
	if country='PL' then country_name='POLAND';
	if country='PT' then country_name='PORTUGAL';
	if country='QA' then country_name='QATAR ';
	if country='RU' then country_name='RUSSIA';
	if country='SA' then country_name='SAUDI ARABIA';
	if country='SE' then country_name='SWEDEN';
	if country='SG' then country_name='SINGAPORE ';
	if country='TH' then country_name='THAILAND';
	if country='TK' then country_name='TURKEY';
	if country='TW' then country_name='TAIWAN';
	if country='ZA' then country_name='SOUTH AFRICA';
	if country='US' then country_name='UNITED STATES';
data tmp5; retain country_name; set tmp5; run;

title "MOM REV profit correlation by country"; 
proc print data=tmp5; run; 
